﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class RuleAbsence
    {
        Common common = new Common();
        Employee employee = new Employee();
        Absence absence = new Absence();
        RuleApprove ruleApprove = new RuleApprove();
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);

        public List<rule_absence> GetRuleAbsence(string rule_absence_seq, string emp_type_seq, string absence_type_seq
            , bool isGetHrmPsEmployeeType, bool isGetHrmPsAbsenceType, bool isGetRuleApproveType)
        {
            DataTable dtb;
            List<rule_absence> list = new List<rule_absence>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@rule_absence_seq", rule_absence_seq);
                dbManager.AddParameters(2, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(3, "@absence_type_seq", absence_type_seq);

                string strSQL = "select * from rule_absence where record_status <> @delete_status"
                    + (!string.IsNullOrWhiteSpace(rule_absence_seq) ? " and rule_absence_seq = @rule_absence_seq" : "")
                    + (!string.IsNullOrWhiteSpace(emp_type_seq) ? " and emp_type_seq = @emp_type_seq" : "")
                    + (!string.IsNullOrWhiteSpace(absence_type_seq) ? " and absence_type_seq = @absence_type_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<rule_absence>)CollectionHelper.ConvertTo<rule_absence>(dtb);

                foreach (rule_absence info in list)
                {
                    if (isGetHrmPsEmployeeType && info.emp_type_seq != null)
                    {
                        List<hrm_ps_employee_types> sub_list = employee.GetHrmPsEmployeeTypes(info.emp_type_seq.ToString());

                        info.hrm_ps_employee_types = sub_list.Count > 0 ? sub_list[0] : null;
                    }

                    if (isGetHrmPsAbsenceType && info.absence_type_seq != null)
                    {
                        List<hrm_ps_absence_type> sub_list = absence.GetHrmPsAbsenceType(info.absence_type_seq.ToString());

                        info.hrm_ps_absence_type = sub_list.Count > 0 ? sub_list[0] : null;
                    }

                    if (isGetRuleApproveType && info.rule_approve_type_seq != null)
                    {
                        List<rule_approve_type> sub_list = ruleApprove.GetRuleApproveType(info.rule_approve_type_seq.ToString(), null, false);

                        info.rule_approve_type = sub_list.Count > 0 ? sub_list[0] : null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public string InsertRuleAbsence(string emp_type_seq, string absence_type_seq
            , string send_before, string include_nwd, string day_limit, string day_limit_full_salary
            , string day_limit_half_salary, string time_limit, string day_per_time_limit
            , string remarks, string record_status, string created_by)
        {
            string strSeq = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@rule_absence_seq", null);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(2, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(3, "@send_before", send_before);
                dbManager.AddParameters(4, "@include_nwd", include_nwd);
                dbManager.AddParameters(5, "@day_limit", day_limit);
                dbManager.AddParameters(6, "@day_limit_full_salary", day_limit_full_salary);
                dbManager.AddParameters(7, "@day_limit_half_salary", day_limit_half_salary);
                dbManager.AddParameters(8, "@time_limit", time_limit);
                dbManager.AddParameters(9, "@day_per_time_limit", day_per_time_limit);
                dbManager.AddParameters(10, "@remarks", remarks);
                dbManager.AddParameters(11, "@record_status", record_status);
                dbManager.AddParameters(12, "@created_by", created_by);
                dbManager.AddParameters(13, "@created_when", DateTime.Now);

                string strSQL =
@"insert into rule_absence
(emp_type_seq, absence_type_seq, send_before
, include_nwd, day_limit, day_limit_full_salary
, day_limit_half_salary, time_limit, day_per_time_limit
, remarks, record_status, created_by, created_when)
values(@emp_type_seq, @absence_type_seq, @send_before
, @include_nwd, @day_limit, @day_limit_full_salary
, @day_limit_half_salary, @time_limit, @day_per_time_limit
, @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from rule_absence";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return strSeq;
        }

        public void UpdateRuleAbsence(string rule_absence_seq, string emp_type_seq, string absence_type_seq
            , string send_before, string include_nwd, string day_limit, string day_limit_full_salary
            , string day_limit_half_salary, string time_limit, string day_per_time_limit
            , string remarks, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@rule_absence_seq", rule_absence_seq);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(2, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(3, "@send_before", send_before);
                dbManager.AddParameters(4, "@include_nwd", include_nwd);
                dbManager.AddParameters(5, "@day_limit", day_limit);
                dbManager.AddParameters(6, "@day_limit_full_salary", day_limit_full_salary);
                dbManager.AddParameters(7, "@day_limit_half_salary", day_limit_half_salary);
                dbManager.AddParameters(8, "@time_limit", time_limit);
                dbManager.AddParameters(9, "@day_per_time_limit", day_per_time_limit);
                dbManager.AddParameters(10, "@remarks", remarks);
                dbManager.AddParameters(11, "@record_status", record_status);
                dbManager.AddParameters(12, "@updated_by", updated_by);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);
                

                string strSQL =
@"update rule_absence
set emp_type_seq = @emp_type_seq
, absence_type_seq = @absence_type_seq
, send_before = @send_before
, include_nwd = @include_nwd
, day_limit = @day_limit
, day_limit_full_salary = @day_limit_full_salary
, day_limit_half_salary = @day_limit_half_salary
, time_limit = @time_limit
, day_per_time_limit = @day_per_time_limit 
, remarks = @remarks
, record_status = @record_status 
, updated_by = @updated_by
, updated_when = @updated_when  
where rule_absence_seq = @rule_absence_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public void UpdateStatusRuleAbsence(string rule_absence_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@rule_absence_seq", rule_absence_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
@"update rule_absence
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_absence_seq = @rule_absence_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }
    }
}
